package util;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.hxtt.concurrent.ad;

import bean.Menu;
import bean.contents;


/**
*	数据库连接 工具 
*
*/
public class Conn2 {

	//测试
	public static void main(String[] args) {
//		getConn();
		try {
			add();
			query(null);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//获取数据库连接对象
	public static Connection getConn() throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
		// --Access 数据库路径  
        String dbpath = "C:\\11.mdb";   //路劲最好不要出现中文，本人测试过程中中文报错！  
        // --连接字符串  
        String url = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="  
                + dbpath;  
        Properties properties = new Properties() ;  
        properties.setProperty("charSet", "gbk") ;          //这里设置是为了防止查询的时候出现乱码  
        Connection conn = null;  
        try {  
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        }  
        conn = DriverManager.getConnection(url,properties);  
        return conn;
        
	}
	
	//新增留言
	public static void add() throws SQLException, ClassNotFoundException, UnsupportedEncodingException{
		Connection conn = getConn();
		if(null != conn){
			String sql = "insert into menu(name,price) values(?,?)";
			PreparedStatement pstat = conn.prepareStatement(sql);
			
			pstat.setString(1, "宫保鸡丁");
			pstat.setBigDecimal(2, new BigDecimal("15"));
			
			pstat.execute();
			pstat.close();
		    conn.close();
		}
	}
	
	
	//查询留言留言
	public static List<Menu> query(String lastId) throws ClassNotFoundException, SQLException, UnsupportedEncodingException{
		Connection conn = getConn();
		List<Menu> l = new ArrayList<Menu>();
		if(null != conn){
			String sql = " SELECT *  FROM menu WHERE 1=1";
			if(null != lastId && lastId.length() > 0){
				sql += " and id <"+lastId+" ";//首次拉取数据lastId 为空
			}
			sql += "  ORDER BY id DESC   ";
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			//处理结果
	        while(rs.next()){
	            //每次循环，都取出一条记录
	        	Menu m = new Menu();
	        	m.setId(rs.getInt("id"));
	        	m.setName(rs.getString("name"));
	        	m.setPrice(rs.getBigDecimal("price"));
	        	System.out.println("id:"+m.getId()+"  名称:"+m.getName()+"  价格："+m.getPrice());
	        	l.add(m);//放入list
	        }
	        
	        rs.close();
	        st.close();
	        conn.close();

		}
		return l;
	}
	
}
